CREATE PROC SP_THU_TIEN_CHI_TIEU
@ID INT
AS
BEGIN
	UPDATE CHI_TIEU
	SET ACTIVE = 'C' -- COMPLETE 
	WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID
END

CREATE FUNCTION F_GET_NGAY_KET_THUC
(
	@ID INT
)
RETURNS DATE
AS
BEGIN
	RETURN (SELECT ISNULL(MAX(NGAY_KET_THUC_KY_NAY),GETDATE()) 
	FROM dbo.THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID)
END

ALTER PROC [dbo].[SP_GET_INFOR_BY_THUE_PHONG]
@ID INT
AS
BEGIN
	SELECT 
		THUE_PHONG.MA_PHONG,
		PHONG.SO_PHONG,
		THUE_PHONG.ID, 
		KHACH_THUE.HO_VA_TEN,
		DBO.F_GET_CHI_SO_NUOC(THUE_PHONG.ID) CHI_SO_NUOC, 
		DBO.F_GET_CHI_SO_DIEN(THUE_PHONG.ID) CHI_SO_DIEN,
		DBO.F_GET_NGAY_KET_THUC(THUE_PHONG.ID) NGAY_KET_THUC
	FROM
	THUE_PHONG,
	KHACH_THUE, 
	PHONG
	WHERE
	THUE_PHONG.ACTIVE = 'A'
	AND KHACH_THUE.ACTIVE = 'A'
	AND KHACH_THUE.LEVEL = 1
	AND THUE_PHONG.ID = @ID
	AND THUE_PHONG.ID = KHACH_THUE.SO_THUE_PHONG
	AND THUE_PHONG.MA_PHONG = PHONG.ID	
END


ALTER PROC [dbo].[SP_INSERT_THU_TIEN]
@SO_THUE_PHONG INT, 
@NGAY_THU DATE, 
@NGUOI_THU INT, 
@NGAY_KT_KY_TRUOC DATE, 
@NGAY_KET_THUC_KY_NAY DATE, 
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT,
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@DON_GIA_NUOC FLOAT, 
@TIEN_PHONG FLOAT,
@TIEN_GUI_XE FLOAT,
@TIEN_PHU_THU FLOAT,
@SO_THANG INT
AS
BEGIN
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG,
		NGAY_THU,
		NGUOI_THU,
		NGAY_KT_KY_TRUOC,
		NGAY_KET_THUC_KY_NAY,
		CHI_SO_NUOC_TRUOC,
		CHI_SO_NUOC_SAU,
		CHI_SO_DIEN_TRUOC,
		CHI_SO_DIEN_SAU,
		DON_GIA_DIEN,
		DON_GIA_NUOC,
		TIEN_PHONG,
		TIEN_GUI_XE,
		TIEN_PHU_THU,
		SO_THANG,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG , 
		@NGAY_THU , 
		@NGUOI_THU , 
		@NGAY_KT_KY_TRUOC , 
		@NGAY_KET_THUC_KY_NAY , 
		@CHI_SO_NUOC_TRUOC , 
		@CHI_SO_NUOC_SAU ,
		@CHI_SO_DIEN_TRUOC , 
		@CHI_SO_DIEN_SAU ,
		@DON_GIA_DIEN , 
		@DON_GIA_NUOC , 
		@TIEN_PHONG ,
		@TIEN_GUI_XE ,
		@TIEN_PHU_THU ,
		@SO_THANG,
		@NGUOI_THU,
		@NGUOI_THU,
		GETDATE(), 
		GETDATE(), 
		'A' 
	)	
	
	DELETE THU_TIEN 
	WHERE ACTIVE = 'P' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
	UPDATE CHI_TIEU
	SET ACTIVE = 'C' -- COMPLETE 
	WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
END

CREATE PROC [dbo].SP_PRINT_BEFORE
@SO_THUE_PHONG INT, 
@NGAY_THU DATE, 
@NGUOI_THU INT, 
@NGAY_KT_KY_TRUOC DATE, 
@NGAY_KET_THUC_KY_NAY DATE, 
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT,
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@DON_GIA_NUOC FLOAT, 
@TIEN_PHONG FLOAT,
@TIEN_GUI_XE FLOAT,
@TIEN_PHU_THU FLOAT,
@SO_THANG INT
AS
BEGIN
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG,
		NGAY_THU,
		NGUOI_THU,
		NGAY_KT_KY_TRUOC,
		NGAY_KET_THUC_KY_NAY,
		CHI_SO_NUOC_TRUOC,
		CHI_SO_NUOC_SAU,
		CHI_SO_DIEN_TRUOC,
		CHI_SO_DIEN_SAU,
		DON_GIA_DIEN,
		DON_GIA_NUOC,
		TIEN_PHONG,
		TIEN_GUI_XE,
		TIEN_PHU_THU,
		SO_THANG,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG , 
		@NGAY_THU , 
		@NGUOI_THU , 
		@NGAY_KT_KY_TRUOC , 
		@NGAY_KET_THUC_KY_NAY , 
		@CHI_SO_NUOC_TRUOC , 
		@CHI_SO_NUOC_SAU ,
		@CHI_SO_DIEN_TRUOC , 
		@CHI_SO_DIEN_SAU ,
		@DON_GIA_DIEN , 
		@DON_GIA_NUOC , 
		@TIEN_PHONG ,
		@TIEN_GUI_XE ,
		@TIEN_PHU_THU ,
		@SO_THANG,
		@NGUOI_THU,
		@NGUOI_THU,
		GETDATE(), 
		GETDATE(), 
		'P' 
	)	
END

ALTER PROC [dbo].[SP_GET_THUE_PHONG_LIST]
@SEARCH_STRING NVARCHAR(MAX)
AS
BEGIN
	SELECT
		THUE_PHONG.ID, 
		THUE_PHONG.MA_PHONG, 
		KHACH_THUE.HO_VA_TEN,
		KHACH_THUE.HINH_ANH,
		PHONG.SO_PHONG,
		ISNULL(THU_TIEN.NGAY_THU, GETDATE()) NGAY_THU, 
		ISNULL(THU_TIEN.NGAY_KET_THUC_KY_NAY,GETDATE()) NGAY_KET_THUC_KY_NAY
	FROM 
		THUE_PHONG
		JOIN PHONG ON THUE_PHONG.MA_PHONG = PHONG.ID
		LEFT JOIN THU_TIEN 
		ON THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG AND THU_TIEN.ID = (SELECT MAX(ID) FROM THU_TIEN WHERE ACTIVE = 'A' AND THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG) 
		JOIN KHACH_THUE ON THUE_PHONG.ID = KHACH_THUE.SO_THUE_PHONG		
	WHERE
		THUE_PHONG.ACTIVE = 'A'
		AND KHACH_THUE.LEVEL = 1
		AND THUE_PHONG.NGAY_TRA IS NULL
		AND (@SEARCH_STRING = '' OR @SEARCH_STRING IS NULL OR UPPER(KHACH_THUE.HO_VA_TEN) LIKE '%' + UPPER(@SEARCH_STRING) + '%' OR  CAST(PHONG.SO_PHONG AS NVARCHAR) = @SEARCH_STRING )
		AND KHACH_THUE.ACTIVE = 'A'
	ORDER BY THU_TIEN.NGAY_KET_THUC_KY_NAY
END


alter PROC SP_GET_PRINT
@ID INT
AS
SELECT TOP  1
ISNULL(CHI_SO_NUOC_TRUOC, 0) CHI_SO_NUOC_TRUOC,
ISNULL(CHI_SO_NUOC_SAU, 0) CHI_SO_NUOC_SAU,
ISNULL(CHI_SO_NUOC_SAU,0) - ISNULL(CHI_SO_NUOC_TRUOC,0) TIEU_THU_NUOC, 
ISNULL(CHI_SO_DIEN_TRUOC, 0) CHI_SO_DIEN_TRUOC,
ISNULL(CHI_SO_DIEN_SAU, 0) CHI_SO_DIEN_SAU, 
ISNULL(CHI_SO_DIEN_SAU,0) - ISNULL(CHI_SO_DIEN_TRUOC,0) TIEU_THU_DIEN
FROM
dbo.THU_TIEN
WHERE ACTIVE= 'P' AND SO_THUE_PHONG = @ID

exec SP_GET_PRINT 1


ALTER PROC SP_GET_CUS_HIST
@ID INT
AS
BEGIN
	SELECT 
	NGAY_THU, 
	NGUOI_THU, 
	FULL_NAME, 
	(ISNULL(CHI_SO_NUOC_SAU,0) - ISNULL(CHI_SO_NUOC_TRUOC,0))*ISNULL(DON_GIA_NUOC,0) TIEN_NUOC, 
	(ISNULL(CHI_SO_DIEN_SAU,0) - ISNULL(CHI_SO_DIEN_TRUOC,0))*ISNULL(DON_GIA_DIEN,0) TIEN_DIEN, 
	ISNULL(TIEN_PHONG, 0) TIEN_PHONG,
	ISNULL(SO_THANG, 0) SO_THANG,
	ISNULL(TIEN_PHU_THU,0) TIEN_PHU_THU
	FROM 
	THU_TIEN, 
	USR
	WHERE
	THU_TIEN.ACTIVE = 'A'
	AND THU_TIEN.NGUOI_THU = USR.ID
	AND THU_TIEN.SO_THUE_PHONG = @ID
END